﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

/*	stub for all new functions. this will ensure that CommandBehaviour will work and that a connection is valid
			//prepare return value

			//Check if open
			if (_connectionCheckOpen())
			{
			}
			_connectionAutomaticClose();

			//return return value ;)
 */

namespace CSharp.Libs.Database
{
    /// <summary>
    /// 
    /// </summary>
	/// <history>19.08.2014 VB: did a lot of redesign and optimization regarding usings and made use of ConnectionBehaviour through the class</history>
	public class SQLiteHandler : IDisposable
    {
        #region declarations
        private SQLiteConnection _SqlConn;
        private ConnectionBehaviour _enmConnectionBehaviour = ConnectionBehaviour.AllwaysOpen; //default
        private string _strConnectionString;
        #endregion



        #region constructor / dispose
        public SQLiteHandler(
            string DatabaseFileName)
        {
            _initConnection(DatabaseFileName);
        }
        public SQLiteHandler(
            string DatabaseFileName,
            ConnectionBehaviour ConnectionMode)
        {
            _enmConnectionBehaviour = ConnectionMode;
            _initConnection(DatabaseFileName);
        }
        public SQLiteHandler(
            string DatabaseFileName,
            ConnectionBehaviour ConnectionMode,
            string CreateSqlString)
        {
            _enmConnectionBehaviour = ConnectionMode;
            _initConnection(DatabaseFileName);
            throw new NotImplementedException();
        }

        #region dispose pattern (http://www.codeproject.com/Articles/15360/Implementing-IDisposable-and-the-Dispose-Pattern-P)
        // some fields that require cleanup
        private bool disposed = false; // to detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposed)
            {
				if (disposing)
				{
					// clean up managed handles
					if (_SqlConn != null)
					{
						switch (_SqlConn.State)
						{
							case System.Data.ConnectionState.Open:
								_SqlConn.Close();
								break;

							case System.Data.ConnectionState.Broken:
							case System.Data.ConnectionState.Connecting:
								break;

							case System.Data.ConnectionState.Closed:
							default:

								break;
						}

						_SqlConn = null;
					}
				}

                disposed = true;
            }
        }

        public void Dispose()
        {
            Dispose(true);
        }
        #endregion
        #endregion



        #region private functions
        #region         connection
        private void _initConnection(
            string DatabaseFilename)
		{
			try
			{
				_strConnectionString = String.Concat("Data Source=", DatabaseFilename, ";");
				_SqlConn = new SQLiteConnection(_strConnectionString);
			}
			catch (BadImageFormatException ex)
			{
				//Most likely one of the projects is build in a different platform (32bit/64bit), or the "32-bit bevorzugen" checkbox is set and every other project is in 64bit.
				throw ex;
			}
			catch (DllNotFoundException ex)
			{
				if (ex.Message.Contains("SQLite.Interop.dll"))
				{
					//Interop dll is missing in target folder i.e. bin\debug
				}
				throw ex;
			}

            if (_enmConnectionBehaviour == ConnectionBehaviour.AllwaysOpen)
            {
                _SqlConn.Open();
            }

            //set default
            ValidateValueTypes = true;
        }

        private void _openConnection()
        {
            if (_enmConnectionBehaviour == ConnectionBehaviour.Manually) { _SqlConn.Open(); }
        }

        private void _closeConnection()
        {
            if (_enmConnectionBehaviour == ConnectionBehaviour.Manually) { _SqlConn.Close(); }
        }

		private bool _connectionCheckOpen()
		{
            //Automatic open?
            if (_SqlConn.State == System.Data.ConnectionState.Closed &&
                _enmConnectionBehaviour == ConnectionBehaviour.AutomaticOpenAndClose)
            {
                _SqlConn.Open();
            }

            //Check if open
			if (_SqlConn.State == System.Data.ConnectionState.Open)
			{
				return true;
			}

			return false;
		}

		private void _connectionAutomaticClose()
		{
			//Automatic close?
			if (_SqlConn.State != System.Data.ConnectionState.Closed &&
				_enmConnectionBehaviour == ConnectionBehaviour.AutomaticOpenAndClose)
			{
				_SqlConn.Close();
			}
		}
        #endregion

        #region         sql execution
        private bool _executeSql(
            string SqlString)
        {
            //declare return value
            bool fExecutionSuccessfull = false;

            //Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommandBuilder scb = new SQLiteCommandBuilder(new SQLiteDataAdapter(SqlString, _SqlConn)))
				{
					using (SQLiteCommand sc = new SQLiteCommand(SqlString, _SqlConn))
					{
						try
						{
							int result = sc.ExecuteNonQuery();
							fExecutionSuccessfull = true;
						}
						catch { }
					}
				}
			}
			_connectionAutomaticClose();

            return fExecutionSuccessfull;
        }
        #endregion

        #region         read data
		private SQLiteDataReader _getDataReader(
			string sqlString)
		{
			//prepare return value
			SQLiteDataReader DR = null;

			//Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommand CMD = new SQLiteCommand(sqlString, _SqlConn))
				{
					DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default);
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
			return DR;

		}

        private DataTable _getDataTable(
            string sqlString)
        {
			//prepare return value
			DataTable DT = null;

            //Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteDataAdapter DA = new SQLiteDataAdapter(sqlString, _SqlConn))
				{
					using (SQLiteCommandBuilder CB = new SQLiteCommandBuilder(DA))
					{
						DT = new DataTable();
						DA.Fill(DT);
					}
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
			return DT;
        }

		private bool _hasRows(
			string sqlString)
		{
			//prepare return value
			bool fHasRows = false;

			//Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommand CMD = new SQLiteCommand(sqlString, _SqlConn))
				{
					using (SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default))
					{
						fHasRows = DR.HasRows;
					}
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
			return fHasRows;
		}

		private bool _hasRows(
			string PreparedStatement,
			ArrayList SqlParameters)
		{
			//prepare return value
			bool fHasRows = false;

			//Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommand cmd = _SqlConn.CreateCommand())
				{
					cmd.CommandText = PreparedStatement;
					cmd.Connection = _SqlConn;

					foreach (SqlParameter SqlParam in SqlParameters)
					{
						cmd.Parameters.Add(SqlParam.Name, SqlParam.Type);
						cmd.Parameters[SqlParam.Name].Value = SqlParam.Value;
					}

					using (SQLiteDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.Default))
					{
						fHasRows = dr.HasRows;
					}
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
			return fHasRows;
		}
		#endregion

        #region write data
        private int _writeDataToTable(
            string TableName,
            Dictionary<string, object> Values)
        {
			//prepare return value
			int intRowsAffected = 0;

			//Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommandBuilder builder = new SQLiteCommandBuilder(new SQLiteDataAdapter("SELECT * FROM " + TableName, _SqlConn)))
				using (SQLiteCommand scInsert = builder.GetInsertCommand())
				{
					foreach (KeyValuePair<string, object> kvp in Values)
					{
						foreach (SQLiteParameter prm in scInsert.Parameters)
						{
							if (prm.SourceColumn == kvp.Key)
							{
								if (ValidateValueTypes)
								{
									if (_dataIsValid(prm, kvp.Value))
									{
										prm.Value = kvp.Value;
									}
								}
								else
								{
									prm.Value = kvp.Value;
								}

								break;
							}
						}
					}

					intRowsAffected = scInsert.ExecuteNonQuery();
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
            return intRowsAffected;
        }

		private int _executePreparedStatement(
			string PreparedStatement,
			ArrayList SqlParameters)
		{
			//prepare return value
			int intRowsAffected = 0;

			//Check if open
			if (_connectionCheckOpen())
			{
				using (SQLiteCommand cmd = _SqlConn.CreateCommand())
				{
					cmd.CommandText = PreparedStatement;
					cmd.Connection = _SqlConn;

					foreach (SqlParameter SqlParam in SqlParameters)
					{
						cmd.Parameters.Add(SqlParam.Name, SqlParam.Type);
						cmd.Parameters[SqlParam.Name].Value = SqlParam.Value;
					}

					try
					{
						intRowsAffected = cmd.ExecuteNonQuery();
					}
					catch (SQLiteException ex)
					{
						//Database is locked sometimes when connection always open ?????
						throw ex;
					}
				}
			}
			_connectionAutomaticClose();

			//return return value ;)
			return intRowsAffected;
		}

        private void _test()
        {
            //_SqlConn.LastInsertRowId
        }
        #endregion

        #region         data handling
        private bool _dataIsValid(
            SQLiteParameter prm,
            object Value)
        {
            //validate integer types
            if (prm.DbType == DbType.Int16 && Value is Int16) return true;
            if (prm.DbType == DbType.Int32 && (Value is Int32 || Value is int)) return true;
            if (prm.DbType == DbType.Int64 && Value is Int64) return true;
            if (prm.DbType == DbType.UInt16 && Value is UInt16) return true;
            if (prm.DbType == DbType.UInt32 && Value is UInt32) return true;
            if (prm.DbType == DbType.UInt64 && Value is UInt64) return true;
            if (prm.DbType == DbType.AnsiString && Value is string) return true;
            if (prm.DbType == DbType.AnsiStringFixedLength && Value is string) return true;
            if (prm.DbType == DbType.Binary && Value is object) return true;
            if (prm.DbType == DbType.Boolean && Value is Boolean) return true;
            if (prm.DbType == DbType.Byte && Value is byte) return true;
            if (prm.DbType == DbType.Date && Value is DateTime) return true;
            if (prm.DbType == DbType.DateTime && Value is DateTime) return true;
            if (prm.DbType == DbType.DateTime2 && Value is DateTime) return true;
            if (prm.DbType == DbType.DateTimeOffset && Value is DateTimeOffset) return true;
            if (prm.DbType == DbType.Double && Value is double) return true;
            if (prm.DbType == DbType.Object && Value is object) return true;
            if (prm.DbType == DbType.SByte && Value is SByte) return true;
            if (prm.DbType == DbType.Single && Value is Single) return true;
            if (prm.DbType == DbType.String && Value is string) return true;
            if (prm.DbType == DbType.StringFixedLength && Value is string) return true;
            if (prm.DbType == DbType.Time && Value is DateTime) return true;

            /* ??? unsure if these types really fit together ??? */
            if (prm.DbType == DbType.Currency && Value is decimal) return true;
            if (prm.DbType == DbType.Decimal && Value is decimal) return true;
            if (prm.DbType == DbType.Guid && Value is Guid) return true; // ??? Does that work ???
            if (prm.DbType == DbType.VarNumeric && (
                Value is Int16 ||
                Value is Int32 ||
                Value is Int64 ||
                Value is UInt16 ||
                Value is UInt32 ||
                Value is UInt64)) return true;
            if (prm.DbType == DbType.Xml && Value is string) return true;
            /* ??? unsure if these types really fit together ??? */

            return false;
        }
        #endregion
        #endregion



        #region properties
        #region         settings
        public bool ValidateValueTypes { get; set; }
        #endregion

        #region         connection
        /// <summary>represents the connection to the database</summary>
        /// <value>SQLiteConnection object</value>
        public SQLiteConnection Connection { get { return _SqlConn; } }

        public ConnectionBehaviour ConnectionMode { get { return _enmConnectionBehaviour; } }

        public ConnectionState ConnectionState { get { return _SqlConn.State; } }

        public long LastInsertedRowId { get { return _SqlConn.LastInsertRowId; } }
        #endregion



        #endregion



        #region methods
        #region         connection
        public void Connection_Open()
        {
            _openConnection();
        }

        public void Connection_Close()
        {
            _closeConnection();
        }
        #endregion

        #region         sql execution
        public bool ExecuteSql(
            string SqlString)
        {
            return _executeSql(SqlString);
        }
        #endregion

        #region         read data
        public SQLiteDataReader GetDataReader(
            string SqlString)
        {
            return _getDataReader(SqlString);
        }

        public DataTable GetDataTable(
            string sqlString)
        {
            return _getDataTable(sqlString);
        }

        public bool HasRows(
            string sqlString)
        {
            return _hasRows(sqlString);
        }
		public bool HasRows(
			string PreparedStatement,
			ArrayList SqlParameters)
		{
			return _hasRows(PreparedStatement, SqlParameters);
		}
        #endregion

        #region write data
        /// <summary>
        /// 
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Values"></param>
        /// <returns>count of affected rows</returns>
        public int WriteDataToTable(
            string TableName,
            Dictionary<string, object> Values)
        {
            return _writeDataToTable(TableName, Values);
        }

        /// <summary>
        /// executes a prepared SQL statement and returns the count of affected rows
        /// </summary>
        /// <param name="PreparedStatement">SQL prepared statement to execute</param>
        /// <param name="SqlParameters">ArrayList filled with params of type SqlParameter</param>
        /// <returns>count of affected rows</returns>
        public int ExecutePreparedStatement(
            string PreparedStatement,
            ArrayList SqlParameters)
        {
            return _executePreparedStatement(PreparedStatement, SqlParameters);
        }
        #endregion
        #endregion



		#region static methods
		public static void CreateDatabase(
			string DatabaseFileName,
			string SqlStatement)
		{
		}
		#endregion
	}



    public class SqlParameter
    {
        public string Name { get; set; }
        public DbType Type { get; set; }
        public object Value { get; set; }
    }
}
